/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mcet.outliers.database;

import com.mcet.Table;
import com.mcet.outliers.data.MedicalMap;
import com.mcet.outliers.log.Log;

import com.mysql.jdbc.log.Log4JLogger;
import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.concurrent.AbstractExecutorService;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nithya
 */
public class DbManager {

    private static DbManager db;
    private String sqlServer = "localhost";
    private int port = 3306;
    private String dbUser = "root";
    private String dbPassword = "root";
    private String dbpath = "outliers";
    private static final Object lock = new Object();
    private DbManager() {
    }

    public static synchronized DbManager getInstance() {
        if (db == null) {
            db = new DbManager();
        }
        return db;
    }

    public ArrayList<String> getTables(String dbName) {

        ArrayList<String> tables = new ArrayList<String>();

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            if (con != null) {
                DatabaseMetaData md = con.getMetaData();
                ResultSet rs = md.getTables(null, null, "%", null);
                while (rs.next()) {
                    tables.add(rs.getString(3));
                }
            }

        } catch (Exception ex) {
        }
        //tables.add("medical");
        //tables.add("hospital");
        return tables;
    }

    public ArrayList<String> getColumns(String table) {
        ArrayList<String> columns = new ArrayList<String>();


        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            String query = "select * from " + table;
            System.out.println("query=" + query);
            ResultSet result = statement.executeQuery(query);
            ResultSetMetaData metadata = null;
            int columnCount = -1;
            if (result != null) {
                metadata = result.getMetaData();
                columnCount = metadata.getColumnCount();

            }

            Log.debug("Column Count : " + columnCount);

            for (int i = 1; i <= columnCount; i++) {

                String columnName = metadata.getColumnName(i);
                columns.add(columnName);
            }
        } catch (Exception e) {
            Log.error(e.getLocalizedMessage());
        }


        return columns;
    }

    public Table detectOutlier(final MedicalMap map, final String table, final String[] outlierColumns) {
        int rows = 0;
        final Table medTable = new Table(table);
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            String query = "select * from " + table;
            System.out.println("query=" + query);
            ResultSet result = statement.executeQuery(query);
            ResultSetMetaData metadata = null;
            int columnCount = -1;
            if (result != null) {
                metadata = result.getMetaData();
                columnCount = metadata.getColumnCount();

            }

            Log.debug("Column Count : " + columnCount);

            for (int i = 1; i <= columnCount; i++) {

                String type = metadata.getColumnTypeName(i);

                String columnName = metadata.getColumnName(i);
                Log.info("columnName : " + i + " " + columnName);
                if (type.equalsIgnoreCase("varchar")) {
                    medTable.setColumn(columnName, "String");
                }

            }
            Log.debug("tableColumnCount : " + medTable.getColumnCount());
            ExecutorService executor = Executors.newFixedThreadPool(6);
            long startTime = System.currentTimeMillis();
            while (result.next()) {
                rows++;

                final ArrayList<String> data = new ArrayList<String>();
                for (int i = 0; i < medTable.getColumnCount(); i++) {

                    String type = medTable.getType(i);
                    if (type.equals("String")) {
                        data.add(result.getString(medTable.getColumnName(i)));
                        /*if(medTable.getColumnName(i).equals("Diagnosis")){
                        Log.debug("Diagnosis : "+result.getString(medTable.getColumnName(i)));
                        }*/
                    }

                }


                Runnable dbThread = new Runnable() {

                    public void run() {


                        int outlierColumn = 0;
                        float weight[] = new float[outlierColumns.length];
                        for (int i = 0; i < data.size(); i++) {
                            for (String outlierCol : outlierColumns) {
                                if (medTable.getColumnName(i).equals(outlierCol)) {
                                    String dataStr = data.get(i);
                                    weight[outlierColumn] = map.getWeight(dataStr);
                                    outlierColumn++;


                                }
                            }

                        }
 


                        float token = getToken(weight);
                        float treshold = token / 2;
                        //Log.debug("Token = "+token);
                        //Log.debug("treshold = "+treshold);
                        boolean outlier = false;
                        for (int i = 0; i < weight.length; i++) {
                            weight[i] = weight[i] - token;
                            if (treshold != 0 && weight[i] >= treshold && weight[i] != 0) {
                                outlier = true;
                                break;
                            }
                        }

                        if (outlier) {
                            
                            synchronized(lock) {
                                
                            
                            Log.debug("....OUTLIER FOUND....");
                            for (int i = 0; i < weight.length; i++) {

                                Log.debug("weight " + weight[i]);

                            }
                            Log.debug("Token = " + token);
                            Log.debug("treshold = " + treshold);
                            Log.debug("============================================");
                            Log.debug("data :: \n");
                            ArrayList<String> outlierDatas = new ArrayList<String>();
                            for (int i = 0; i < data.size(); i++) {

                                for (String outlierCol : outlierColumns) {
                                    if (medTable.getColumnName(i).equals(outlierCol)) {
                                        outlierDatas.add(data.get(i));

                                    }

                                }




                            }
                            ArrayList<String> possibleValue = map.findPossibleValue(outlierDatas);
                            medTable.setOutlier(data);
                            medTable.setPossibleValue(possibleValue);
                            lock.notify();
                            }
                        }
                    }
                };
                executor.execute(dbThread);


            }

            result.close();
            statement.close();
            con.close();
            try {
               // executor.awaitTermination(1, TimeUnit.SECONDS);
                 executor.shutdown();
                long endTime = System.currentTimeMillis();
                Log.info("Time taken : " + (endTime - startTime));
            } catch (Exception ex) {
                Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
            }

        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        Log.debug("Row Processed :" + rows);


        return medTable;
    }

    public ArrayList<String> getColumnNames(String table) {
        Table medTable = new Table(table);
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            String query = "select * from " + table;
            System.out.println("query=" + query);
            ResultSet result = statement.executeQuery(query);
            ResultSetMetaData metadata = null;
            int columnCount = -1;
            if (result != null) {
                metadata = result.getMetaData();
                columnCount = metadata.getColumnCount();

            }

            Log.debug("Column Count : " + columnCount);

            for (int i = 1; i <= columnCount; i++) {

                String type = metadata.getColumnTypeName(i);

                String columnName = metadata.getColumnName(i);
                Log.info("columnName : " + i + " " + columnName);
                if (type.equalsIgnoreCase("varchar")) {
                    medTable.setColumn(columnName, "String");
                }

            }

        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return medTable.getColumns();
    }

    private float getToken(float weight[]) {
        int count[] = new int[weight.length];
        for (int i = 0; i < weight.length; i++) {
            count[i] = 1;
            for (int j = i + 1; j < weight.length; j++) {
                if (weight[i] == weight[j]) {
                    count[i]++;
                }
            }
        }

        /*  System.out.print("Count :");
        for(int i=0;i<count.length;i++) {
        System.out.print(" "+count[i]);
        }
        System.out.println("");*/
        int max = findMax(count);
        // Log.info("Max = "+max);
        int index = findIndex(count, max);
        float token = weight[index];

        return token;
    }

    private int findMax(int count[]) {

        int large = count[0];

        for (int i = 1; i < count.length; i++) {
            if (count[i] > large) {
                large = count[i];
            }
        }
        return large;
    }

    private int findIndex(int count[], int num) {

        int index = 0;

        for (index = 0; index < count.length; index++) {
            if (count[index] == num) {
                return index;
            }
        }


        return index;

    }

    public String getDbPassword() {
        return dbPassword;
    }

    public void setDbPassword(String dbPassword) {
        this.dbPassword = dbPassword;
    }

    public String getDbUser() {
        return dbUser;
    }

    public void setDbUser(String dbUser) {
        this.dbUser = dbUser;
    }

    public String getDbpath() {
        return dbpath;
    }

    public void setDbpath(String dbpath) {
        this.dbpath = dbpath;
    }

    public int getPort() {
        return port;
    }

    public void setPort(int port) {
        this.port = port;
    }

    public String getSqlServer() {
        return sqlServer;
    }

    public void setSqlServer(String sqlServer) {
        this.sqlServer = sqlServer;
    }

    public boolean isCredentialCorrect() {
        Log.debug("isCredentialCorrect-start");
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            if (statement != null) {
                Log.info("DB connected");
                Log.debug("SQL Server ::: " + sqlServer);
                Log.debug("port ::: " + port);
                Log.debug("dbpath ::: " + dbpath);
                Log.debug("dbUser ::: " + dbUser);
                Log.debug("dbPassword ::: " + dbPassword);

                return true;
            }
        } catch (Exception ex) {
        }

        Log.info("DB not connected");
        Log.debug("SQL Server ::: " + sqlServer);
        Log.debug("port ::: " + port);
        Log.debug("dbpath ::: " + dbpath);
        Log.debug("dbUser ::: " + dbUser);
        Log.debug("dbPassword ::: " + dbPassword);

        return false;
    }

    public Table detectOutlierUsingSingleThread(final MedicalMap map, final String table, final String[] outlierColumns) {
        int rows = 0;
        final Table medTable = new Table(table);
        long startTime = System.currentTimeMillis();
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                    + "/" + dbpath, dbUser, dbPassword);
            Statement statement = con.createStatement();
            String query = "select * from " + table;
            System.out.println("query=" + query);
            ResultSet result = statement.executeQuery(query);
            ResultSetMetaData metadata = null;
            int columnCount = -1;
            if (result != null) {
                metadata = result.getMetaData();
                columnCount = metadata.getColumnCount();

            }

            Log.debug("Column Count : " + columnCount);

            for (int i = 1; i <= columnCount; i++) {

                String type = metadata.getColumnTypeName(i);

                String columnName = metadata.getColumnName(i);
                Log.info("columnName : " + i + " " + columnName);
                if (type.equalsIgnoreCase("varchar")) {
                    medTable.setColumn(columnName, "String");
                }

            }
            Log.debug("tableColumnCount : " + medTable.getColumnCount());

            while (result.next()) {
                rows++;

                final ArrayList<String> data = new ArrayList<String>();
                for (int i = 0; i < medTable.getColumnCount(); i++) {

                    String type = medTable.getType(i);
                    if (type.equals("String")) {
                        data.add(result.getString(medTable.getColumnName(i)));
                        /*if(medTable.getColumnName(i).equals("Diagnosis")){
                        Log.debug("Diagnosis : "+result.getString(medTable.getColumnName(i)));
                        }*/
                    }

                }







                int outlierColumn = 0;
                float weight[] = new float[outlierColumns.length];
                for (int i = 0; i < data.size(); i++) {
                    for (String outlierCol : outlierColumns) {
                        if (medTable.getColumnName(i).equals(outlierCol)) {
                            String dataStr = data.get(i);
                            weight[outlierColumn] = map.getWeight(dataStr);
                            outlierColumn++;


                        }
                    }

                }



                float token = getToken(weight);
                float treshold = token / 2;
                //Log.debug("Token = "+token);
                //Log.debug("treshold = "+treshold);
                boolean outlier = false;
                for (int i = 0; i < weight.length; i++) {
                    weight[i] = weight[i] - token;
                    if (treshold != 0 && weight[i] >= treshold && weight[i] != 0) {
                        outlier = true;
                        break;
                    }
                }

                if (outlier) {
                    Log.debug("....OUTLIER FOUND....");
                    for (int i = 0; i < weight.length; i++) {

                        Log.debug("weight " + weight[i]);

                    }
                    Log.debug("Token = " + token);
                    Log.debug("treshold = " + treshold);
                    Log.debug("============================================");
                    Log.debug("data :: \n");
                    ArrayList<String> outlierDatas = new ArrayList<String>();
                    for (int i = 0; i < data.size(); i++) {

                        for (String outlierCol : outlierColumns) {
                            if (medTable.getColumnName(i).equals(outlierCol)) {
                                outlierDatas.add(data.get(i));

                            }

                        }




                    }
                    ArrayList<String> possibleValue = map.findPossibleValue(outlierDatas);
                    medTable.setOutlier(data);
                    medTable.setPossibleValue(possibleValue);


                }




            }

            result.close();
            statement.close();
            con.close();

        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        Log.debug("Row Processed :" + rows);

        long endTime = System.currentTimeMillis();
        Log.info("Time taken : " + (endTime - startTime));

        return medTable;
    }
     public void deleteOutlier(String whereClause, String table) {

        String deleteQuery = "Delete From " + table + " where " + whereClause;
       Log.debug("Delete Query = "+deleteQuery);
         try {
                  Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection con = DriverManager.getConnection("jdbc:mysql://" + sqlServer + ":" + port
                     + "/" + dbpath, dbUser, dbPassword);
             Statement statement = con.createStatement();
             statement.execute(deleteQuery);
         } catch (Exception ex) {
             ex.printStackTrace();
         }


     }
}
